﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;

namespace BookStore.Controllers
{
    public class LocationController : Controller
    {
        private static LocationController _controller = null;
        public static LocationController getController()
        {
            if (_controller == null)
                return new LocationController();
            else
                return _controller;
        }
        public LocationController()
        {
            _controller = this;
        }

        public DataTable getAllLocation()
        {
            DataTable all = new DataTable("Location");
            con = BSCommon.getConnection();
            con.Open();
            cmd = new SqlCommand("select * from location", con);
            cmd.CommandType = CommandType.Text;
            SqlDataReader sdr = cmd.ExecuteReader();
            all.Load(sdr);
            con.Dispose();
            con.Close();
            return all;
        }

        public String getLocation(String location, int check)
        {
            DataTable dt = new DataTable("GetLocation");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Get LocationName from LocationID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_GetLocationName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = location;
                }
                // Get LocationID from LocationName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_GetLocationID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = location;
                }
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if (dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return "";
        }

        public void AddLocationToDatabase(String LocationID, String LocationName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddLocationToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID;
                cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = LocationName;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void DeleteLocationFromDatabase(String LocationID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DeleteLocationFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Deleted successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to delete this location because it's attribute!", "Error : Delete Location", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyLocationFromDatabase(String LocationID, String LocationName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyLocationFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID;
                cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = LocationName;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Modified successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this location because it's attribute!", "Error : Modify Location", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayLocation(String Location, int check)
        {
            DataTable dt = new DataTable("DisplayLocation");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Location From LocationID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayLocationFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = Location;
                }
                // Display Location From LocationName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_DisplayLocationFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = Location + "%";
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllLocation()
        {
            DataTable dt = new DataTable("DisplayAllAuthor");
            String sql = @"SELECT [LocationID],[LocationName]
                           FROM [BookStore].[dbo].[LOCATION]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckLocationInDatabase(String Location, int check)
        {
            DataTable dt = new DataTable("CheckLocationInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Location From LocationID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckLocationFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = Location;
                }
                // Check Location From LocationName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_CheckLocationFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = Location;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean checkLocationName(String LocationName)
        {
            for (int i = 0; i < LocationName.Length; i++)
            {
                if ((LocationName[i].ToString() == "\\") || (LocationName[i].ToString() == ";") || (LocationName[i].ToString() == ")"))
                    return false;
            }
            return true;
        }

        public DataTable SearchByName(string name)
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Location where LocationName like '" + name + "%';", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
        public DataTable SearchByID(string name)
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Location where LocationID like '" + name + "%';", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }   
   }
}
